package com.bamboo.tool.store.Impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.RuntimeUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import com.alibaba.fastjson.JSONObject;
import com.bamboo.tool.config.RestfulCloudApplicationCache;
import com.bamboo.tool.config.model.ProjectInfo;
import com.bamboo.tool.constant.StringConstant;
import com.bamboo.tool.db.SqliteConfig;
import com.bamboo.tool.db.entity.BambooApiMethod;
import com.bamboo.tool.entity.*;
import com.bamboo.tool.enums.RequestMethod;
import com.bamboo.tool.enums.SoaType;
import com.bamboo.tool.factory.FrameworkExecute;
import com.bamboo.tool.store.StoreService;
import com.bamboo.tool.util.DescProcessUtil;
import com.bamboo.tool.util.StringUtil;
import com.intellij.ide.fileTemplates.impl.UrlUtil;
import com.intellij.openapi.project.Project;
import icons.PluginIcons;
import lombok.SneakyThrows;
import org.jetbrains.annotations.NotNull;

import java.io.File;
import java.net.URL;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Create by GuoQing
 * Date 2022/8/2 16:06
 * Description
 */
public class SqliteStoreServiceImpl implements StoreService {

    @SneakyThrows
    @Override
    public void initTable() {
        String dataPath = RestfulCloudApplicationCache.getDataPath() + "/restfulCloud.db";
        boolean exist = FileUtil.exist(dataPath);
        if (!exist) {
            File touch = FileUtil.touch(dataPath);
        }
        String mdPath = (RestfulCloudApplicationCache.getDataPath() + "/template/markdown/md.ftl");
        boolean mdPathExist = FileUtil.exist(mdPath);
        if (!mdPathExist) {
            URL resource = PluginIcons.class.getResource("/template/markdown/md.ftl");
            String content = UrlUtil.loadText(resource);
            FileUtil.writeBytes(content.getBytes(), mdPath);
        }
        String osName = System.getProperty("os.name");
        if (osName.startsWith("Mac")) {
            RuntimeUtil.exec("sudo chmod -R 777 " + dataPath);
        }
        URL resource = PluginIcons.class.getResource("/initJson/sql.json");
        String tableInfo = UrlUtil.loadText(resource);
        List<TableInfo> tableInfos = JSONObject.parseArray(tableInfo, TableInfo.class);

        Connection connection = SqliteConfig.getConnection();
        Statement state = connection.createStatement();
        Set<String> strings = queryTables();
        tableInfos.stream().forEach(e -> {
            String tableName = e.getTableName();
            if (!strings.contains(tableName)) {
                try {
                    state.addBatch(e.getCreatSql());
                    if (CollUtil.isNotEmpty(e.getIndex())) {
                        for (int i = 0; i < e.getIndex().size(); i++) {
                            state.addBatch(e.getIndex().get(i));
                        }

                    }
                } catch (SQLException ex) {
                    throw new RuntimeException(ex);
                }
            }
        });

        state.executeBatch();
        state.close();
        connection.close();
    }

    @SneakyThrows
    @Override
    public void initData() {
        URL dataResource = PluginIcons.class.getResource("/initJson/data.json");
        String datas = UrlUtil.loadText(dataResource);
        InitTableInfo initTableInfo = JSONUtil.toBean(datas, InitTableInfo.class);
        Map<String, List<InitTableInfo.TableData>> dicts = initTableInfo.getDicts();
        Set<String> dictAll = queryDictAll();
        List<String> sqls = new ArrayList<>();
        dicts.forEach((key, value) -> {
            value.forEach(e -> {
                String code = e.getCode();
                if (!dictAll.contains(key + code)) {
                    sqls.add(e.getSql());
                }
            });
        });
        Map<String, Map<String, Map<String, Map<String, String>>>> map = querySetting();
        List<InitTableInfo.TableData> annotations = initTableInfo.getAnnotations();
        annotations.forEach(framework -> {
            String frameworkCode = framework.getCode();
            Map<String, Map<String, Map<String, String>>> frameworkMap = map.get(frameworkCode);
            if (frameworkMap != null) {
                List<InitTableInfo.TableData> scopeChildren = framework.getChildren();
                scopeChildren.forEach(scope -> {
                    String scopeCode = scope.getCode();
                    Map<String, Map<String, String>> scopeMap = frameworkMap.get(scopeCode);
                    if (scopeMap != null) {
                        scope.getChildren().forEach(classPath -> {
                            String classPathCode = classPath.getCode();
                            Map<String, String> classPathMap = scopeMap.get(classPathCode);
                            if (classPathMap != null) {
                                classPath.getChildren().forEach(attribute -> {
                                    String attributeSring = classPathMap.get(attribute.getCode());
                                    if (StrUtil.isEmpty(attributeSring)) {
                                        InitTableInfo.getAllSql(attribute, sqls);
                                    }
                                });
                            } else {
                                InitTableInfo.getAllSql(classPath, sqls);
                            }
                        });
                    } else {
                        scope.getChildren().forEach(e -> InitTableInfo.getAllSql(e, sqls));
                    }
                });

            } else {
                InitTableInfo.getAllSql(framework, sqls);
            }
        });
        Connection connection = SqliteConfig.getConnection();
        Statement state = connection.createStatement();
        for (String sql : sqls) {
            state.addBatch(sql);
        }
        state.executeBatch();
        state.close();
        connection.close();
    }

    @SneakyThrows
    @Override
    public List<AnnotationInfoSetting> getAllAnnotation() {
        Map<String, List<AnnotationParam>> paramMap = SqliteStoreServiceImpl.selectAllAnnotationParam().stream().collect(Collectors.groupingBy(AnnotationParam::getAnnotationInfoId, Collectors.toList()));
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        String sql = "select ai.id,ai.class_path,ai.class_short_name,ai.scope,ai.soa_type,ai.effect,ai.other_params,f.name frameworkName,f.describe frameworkDescribe from annotation_info_setting  ai inner join framework  f on ai.framework_id = f.id and ai.is_delete=0;";
        ResultSet resultSet = state.executeQuery(sql);
        List<AnnotationInfoSetting> annotationInfoSettings = new ArrayList<>();
        while (resultSet.next()) {
            AnnotationInfoSetting annotationInfoSetting = getAnnotationInfo(resultSet, paramMap);
            annotationInfoSettings.add(annotationInfoSetting);
        }
        resultSet.close();
        state.close();
        conn.close();
        return annotationInfoSettings;
    }

    @SneakyThrows
    @Override
    public List<BambooApiMethod> searchApi(String searchChar, Set<RequestMethod> requestMethods, Set<SoaType> soaTypes, Set<Integer> projectIds, Project project, Boolean isShowDesc) {

        List<String> allDescDort = FrameworkExecute.getAllDescDort();

        List<BambooApiMethod> apis = new ArrayList<>();
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        StringBuilder sql = new StringBuilder();
        sql.append("select ");
        sql.append("bm.method_name,");
        sql.append("bm.method_url,");
        sql.append("bm.request_methods,");
        sql.append("bm.method_params,");
        sql.append("bm.id as methodId,");
        sql.append("bc.class_name,");
        sql.append("bc.id as classId,");
        sql.append("bc.persistence      classPersistence, ");
        sql.append("bm.persistence     methodPersistence, ");

        if (isShowDesc) {
            sql.append("bm.description as methodDesc,");
            sql.append("bc.description as classDesc,");
        }
        sql.append("bc.class_path,");
        sql.append("bc.model_name,");
        sql.append("bp.id as projectId,");
        sql.append("bp.project_name,");
        sql.append("bp.project_path,");
        sql.append("ais.soa_type");
        sql.append(" from bamboo_method bm");
        sql.append(" inner join bamboo_class bc on bm.class_id = bc.id");
        sql.append(" inner join bamboo_project bp on bc.project_id = bp.id");
        sql.append(" inner join annotation_info_setting ais on bc.setting_id = ais.id");
        sql.append(" where 1=1 ");

        StringBuilder where = new StringBuilder();

        if (CollUtil.isNotEmpty(projectIds)) {
            where.append(" and bc.project_id in (");
            String projectIdString = StrUtil.join(",", projectIds);
            where.append(projectIdString);
            where.append(")");
        }
        if (CollUtil.isNotEmpty(requestMethods) && requestMethods.size() != RequestMethod.values().length) {
            where.append(" and (");
            List<RequestMethod> requestMethodList = new ArrayList<>(requestMethods);
            for (int i = 0; i < requestMethodList.size(); i++) {
                RequestMethod requestMethod = requestMethodList.get(i);
                if (i != 0) {
                    where.append(" or ");
                }
                where.append(" bm.request_methods like ");
                where.append(" '%" + requestMethod.getCode() + "%'");
            }
            where.append(") ");
        }
        if (CollUtil.isEmpty(requestMethods)) {
            where.append(" and 1=2");
        }

        if (StrUtil.isNotBlank(searchChar)) {
            searchChar = StrUtil.removeAll(searchChar, "'");
            where.append("and");
            where.append("((bm.method_url like");
            where.append(" '%" + searchChar + "%') or");
            where.append(" (bm.method_name like");
            where.append(" '%" + searchChar + "%') or");
            where.append(" (bc.class_name like");
            where.append(" '%" + searchChar + "%') or");
            where.append(" (bm.description like");
            where.append(" '%" + searchChar + "%') or");
            where.append(" (bc.description like");
            where.append(" '%" + searchChar + "%')");
            where.append(")");
        }

        sql.append(where);
        sql.append(";");
        ResultSet resultSet = state.executeQuery(sql.toString());
        while (resultSet.next()) {

            String methodName = resultSet.getString("method_name");
            String requestMethodsStr = resultSet.getString("request_methods");
            String className = resultSet.getString("class_name");
            String classPath = resultSet.getString("class_path");
            String modelName = resultSet.getString("model_name");
            int projectId = resultSet.getInt("projectId");
            String projectName = resultSet.getString("project_name");
            String projectPath = resultSet.getString("project_path");
            String soaTypeStr = resultSet.getString("soa_type");
            String methodUrl = resultSet.getString("method_url");
            String methodParamsStr = resultSet.getString("method_params");
            String methodId = resultSet.getString("methodId");
            String classId = resultSet.getString("classId");
            String classPersistence = resultSet.getString("classPersistence");
            String methodPersistence = resultSet.getString("methodPersistence");
            BambooApiMethod method = new BambooApiMethod();
            if (isShowDesc) {
                String methodDesc = resultSet.getString("methodDesc");
                String classDesc = resultSet.getString("classDesc");
                Map<String, String> methodDescMap = JSONObject.parseObject(methodDesc, Map.class);
                Map<String, String> classDescMap = JSONObject.parseObject(classDesc, Map.class);
                String methodDescStr = DescProcessUtil.getDesc(allDescDort, methodDescMap);
                String classDescStr = DescProcessUtil.getDesc(allDescDort, classDescMap);
                method.setMethodDesc(methodDescStr);
                method.setClassDesc(classDescStr);
            }
            ClassPersistenceInfo classPersistenceInfo = JSONObject.parseObject(classPersistence, ClassPersistenceInfo.class);
            MethodPersistenceInfo methodPersistenceInfo = JSONObject.parseObject(methodPersistence, MethodPersistenceInfo.class);
            method.setClassPersistenceInfo(classPersistenceInfo);
            method.setMethodPersistenceInfo(methodPersistenceInfo);
            method.setMethodId(methodId);
            method.setClassId(classId);
            method.setClassName(className);
            method.setUrl(methodUrl);
            method.setMethodName(methodName);
            method.setClassPath(classPath);
            method.setModelName(modelName);
            method.setProjectId(projectId);
            method.setProjectPath(projectPath);
            method.setProjectName(projectName);
            SoaType soaType = SoaType.getSoaType(soaTypeStr);
            method.setSoaType(soaType);
            method.setProject(project);
            method.setMethodParams(methodParamsStr);

            Set<String> requestMethod = new HashSet<>(JSONObject.parseArray(requestMethodsStr, String.class));
            method.setRequestMethods(requestMethod);
            apis.add(method);
        }
        return apis;
    }

    @SneakyThrows
    @Override
    public void saveData(ApiClass apiClass, Integer projectId) {

        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        List<String> sqls = new ArrayList<>();
        String classId = UUID.randomUUID().toString();
        apiClass.setId(classId);
        String classInsertSql = apiClass.toInsertSql(projectId);
        sqls.add(classInsertSql);
        apiClass.getMethods().stream().forEach(method -> {
            String methodId = UUID.randomUUID().toString();
            method.setId(methodId);
            String methodInsertSql = method.toInsertSql(projectId.toString(), classId);
            sqls.add(methodInsertSql);
        });
        sqls.forEach(e -> {
            try {
                state.addBatch(e);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        });
        try {
            state.executeBatch();
        } catch (Exception e) {
            e.printStackTrace();
        }

        state.close();
        conn.close();
    }

    @SneakyThrows
    @Override
    public List<ProjectInfo> getAllProjectInfos() {
        List<ProjectInfo> projectInfos = new ArrayList<>();
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        String sql = "select * from bamboo_project;";
        ResultSet resultSet = state.executeQuery(sql);
        while (resultSet.next()) {
            String projectName = resultSet.getString("project_name");
            String projectPath = resultSet.getString("project_path");
            String otherInfo = resultSet.getString("other_info");
            int id = resultSet.getInt("id");
            ProjectInfo info = new ProjectInfo();
            info.setId(id);
            info.setProjectName(projectName);
            info.setProjectPath(projectPath);
            info.setProjectOtherInfo(JSONObject.parseObject(otherInfo, ProjectOtherInfo.class));
            projectInfos.add(info);
        }
        return projectInfos;
    }

    @SneakyThrows
    @Override
    public Collection<ApiClass> getApiClass(QueryClassParameter queryClassParameter) {
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();

        StringBuilder sql = new StringBuilder();
        sql.append("select bc.id       classId, ");
        sql.append("bc.class_name      className, ");
        sql.append("bc.model_name      modelName, ");
        sql.append("bc.class_path      classPath, ");
        sql.append("bc.persistence      classPersistence, ");
        sql.append("bc.project_id      projectId, ");
        sql.append("bp.project_name      projectName, ");
        sql.append("bc.description     classDescription, ");
        sql.append("bc.setting_id      settingId, ");
        sql.append("bc.other_info      classOtherInfo, ");
        sql.append("bm.id              methodId, ");
        sql.append("bm.method_name     methodName, ");
        sql.append("bm.method_params   methodParams, ");
        sql.append("bm.method_url      methodUrl, ");
        sql.append("bm.persistence     methodPersistence, ");
        sql.append("bm.description     methoddescription, ");
        sql.append("bm.request_methods methodRequestMethods, ");
        sql.append("bm.other_info      methodOtherInfo ");
        sql.append("from bamboo_class bc ");
        sql.append("  left join bamboo_method bm on bc.id = bm.class_id ");
        sql.append("  left join bamboo_project bp on bp.id =bc.project_id  ");
        sql.append("where 1=1");
        if (queryClassParameter != null) {
            sql.append(" and (");
            StringBuilder where = new StringBuilder();
            List<String> methodIds = queryClassParameter.getMethodIds();
            if (CollUtil.isNotEmpty(methodIds)) {
                where.append("(bm.id in (");
                for (int i = 0; i < methodIds.size(); i++) {
                    String methodId = methodIds.get(i);
                    if (i != 0) {
                        where.append(",");
                    }
                    where.append("'" + methodId + "'");
                }
                where.append(" ))");
            }
            List<String> classIds = queryClassParameter.getClassIds();
            if (CollUtil.isNotEmpty(classIds)) {
                if (StrUtil.isNotBlank(where.toString())) {
                    where.append(" or ");
                }
                where.append(" (bc.id in (");
                for (int i = 0; i < classIds.size(); i++) {
                    String classId = classIds.get(i);
                    if (i != 0) {
                        where.append(",");
                    }
                    where.append("'");
                    where.append(classId);
                    where.append("'");
                }
                where.append(" ))");
            }

            Map<Integer, List<String>> projects = queryClassParameter.getProjects();
            if (CollUtil.isNotEmpty(projects)) {
                if (StrUtil.isNotBlank(where.toString())) {
                    where.append(" or ");
                }
                where.append(" (");
                Integer index = 0;
                for (Map.Entry<Integer, List<String>> entry : projects.entrySet()) {
                    if (index != 0) {
                        where.append(" or ");
                    }
                    Integer projectId = entry.getKey();
                    if (CollUtil.isNotEmpty(entry.getValue())) {
                        where.append("(bc.project_id ='");
                        where.append(projectId);
                        where.append("' and bc.model_name in (");
                        List<String> modules = entry.getValue();
                        for (int i = 0; i < modules.size(); i++) {
                            if (i != 0) {
                                where.append(",");
                            }
                            where.append("'");
                            where.append(modules.get(i));
                            where.append("'");
                        }
                        where.append("))");
                    } else {
                        where.append("(bm.project_id ='");
                        where.append(projectId);
                        where.append("')");
                    }
                    index++;
                }
                where.append("  )");
            }
            sql.append(where);

            sql.append(")");
        }
        sql.append(";");
        ResultSet resultSet = state.executeQuery(sql.toString());
        Map<String, ApiClass> classMap = new HashMap<>();
        while (resultSet.next()) {
            String classId = resultSet.getString("classId");
            String className = resultSet.getString("className");
            String modelName = resultSet.getString("modelName");
            String classPath = resultSet.getString("classPath");
            String classOtherInfo = resultSet.getString("classOtherInfo");
            String methodId = resultSet.getString("methodId");
            String methodName = resultSet.getString("methodName");
            String methodParams = resultSet.getString("methodParams");
            String classDescription = resultSet.getString("classDescription");
            String methodDescription = resultSet.getString("methoddescription");
            String classPersistence = resultSet.getString("classPersistence");
            String methodPersistence = resultSet.getString("methodPersistence");
            Integer projectId = resultSet.getInt("projectId");

            String methodUrl = resultSet.getString("methodUrl");
            String methodRequestMethods = resultSet.getString("methodRequestMethods");
            String methodOtherInfo = resultSet.getString("methodOtherInfo");
            String projectName = resultSet.getString("projectName");

            ApiClass apiClass = classMap.get(classId);
            if (apiClass == null) {
                apiClass = new ApiClass();
                apiClass.setId(classId);
                apiClass.setClassName(className);
                apiClass.setProjectName(projectName);
                apiClass.setProjectId(projectId.toString());
                apiClass.setModuleName(modelName);
                apiClass.setPersistence(JSONObject.parseObject(classPersistence, ClassPersistenceInfo.class));
                apiClass.setClassPath(classPath);
                apiClass.setDesc(JSONObject.parseObject(classDescription, Map.class));
                apiClass.setClassOtherInfo(JSONObject.parseObject(classOtherInfo, ClassOtherInfo.class));
                classMap.put(classId, apiClass);
            }
            ApiMethod method = new ApiMethod();
            method.setId(methodId);
            method.setMethodName(methodName);
            method.setMethodParams(methodParams);
            method.setMethodUrl(methodUrl);
            method.setPersistence(JSONObject.parseObject(methodPersistence, MethodPersistenceInfo.class));
            method.setDesc(JSONObject.parseObject(methodDescription, Map.class));
            method.setRequestMethods(new HashSet<>(JSONObject.parseArray(methodRequestMethods, String.class)));
            method.setOtherInfo(JSONObject.parseObject(methodOtherInfo, MethodOtherInfo.class));
            apiClass.getMethods().add(method);
        }
        return classMap.values();
    }

    @NotNull
    private static AnnotationInfoSetting getAnnotationInfo(ResultSet resultSet, Map<String, List<AnnotationParam>> paramMap) throws SQLException {
        AnnotationInfoSetting annotationInfoSetting = new AnnotationInfoSetting();
        String id = resultSet.getString("id");
        String classPath = resultSet.getString("class_path");
        String classShortName = resultSet.getString("class_short_name");
        String scope = resultSet.getString("scope");
        String soaType = resultSet.getString("soa_type");
        String effect = resultSet.getString("effect");
        String frameworkName = resultSet.getString("frameworkName");
        String frameworkDescribe = resultSet.getString("frameworkDescribe");
        String otherParams = resultSet.getString("other_params");
        List<AnnotationParam> params = paramMap.get(id);

        annotationInfoSetting.setId(id);
        annotationInfoSetting.setAnnotationPath(classPath);
        annotationInfoSetting.setAnnotationName(classShortName);
        if (scope != null) {
            annotationInfoSetting.setScope(scope);
        }
        annotationInfoSetting.setSoaType(soaType);
        annotationInfoSetting.setEffect(effect);
        if (params != null) {
            annotationInfoSetting.setParams(params);
        }
        if (StringUtil.isNotEmpty(otherParams)) {
            Map<String, List<String>> otherParamMap = JSONObject.parseObject(otherParams, HashMap.class);
            annotationInfoSetting.setOtherParams(otherParamMap);
        }
        if (StringUtil.isNotEmpty(frameworkName)) {
            Framework framework = new Framework();
            framework.setName(frameworkName);
            framework.setDescribe(frameworkDescribe);
            annotationInfoSetting.setFramework(framework);
        }
        return annotationInfoSetting;
    }

    @SneakyThrows
    public static List<AnnotationParam> selectAllAnnotationParam() {
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        String sql = "select ap.* from annotation_param_setting ap;";
        ResultSet resultSet = state.executeQuery(sql);
        List<AnnotationParam> params = new ArrayList<>();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String type = resultSet.getString("type");
            String describe = resultSet.getString("describe");
            String defaultValue = resultSet.getString("default_value");
            String parsingType = resultSet.getString("parsing_type");
            String annotationInfoId = resultSet.getString("annotation_info_setting_id");
            AnnotationParam annotationInfo = new AnnotationParam();
            annotationInfo.setId(id);
            annotationInfo.setName(name);
            annotationInfo.setType(type);
            annotationInfo.setDescribe(describe);
            annotationInfo.setParsingType(parsingType);
            if (StrUtil.isNotBlank(defaultValue)) {
                annotationInfo.setDefaultValues(JSONObject.parseArray(defaultValue, String.class));
            }
            annotationInfo.setAnnotationInfoId(annotationInfoId);
            params.add(annotationInfo);
        }
        resultSet.close();
        state.close();
        conn.close();
        return params;
    }

    @SneakyThrows
    public ProjectInfo queryProject(String projectPath, String projectName) {
        String sql = StringUtil.format("SELECT * FROM bamboo_project where project_path='{}' and project_name='{}' ; ", projectPath, projectName);
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        ResultSet rs = state.executeQuery(sql);
        List<ProjectInfo> projectInfos = new ArrayList<>();
        while (rs.next()) {
            ProjectInfo projectInfo = new ProjectInfo();
            String name = rs.getString("project_name");
            int id = rs.getInt("id");
            String path = rs.getString("project_path");
            projectInfo.setProjectPath(path);
            projectInfo.setProjectName(name);
            projectInfo.setId(id);
            projectInfos.add(projectInfo);
        }
        rs.close();
        conn.close();
        if (projectInfos.size() < 1) {
            ProjectInfo projectInfo = new ProjectInfo();
            projectInfo.setProjectPath(projectPath);
            projectInfo.setProjectName(projectName);
            return saveProject(projectInfo);
        } else {
            return projectInfos.get(0);
        }
    }

    @SneakyThrows
    @Override
    public void deleteInvalidProject() {
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        state.execute("delete from bamboo_project where id in(select bp.id from bamboo_project bp where (select count(bc.id) from bamboo_class bc where bc.project_id = bp.id) < 1) ");
        state.close();
        conn.close();
    }

    @SneakyThrows
    public static void deleteCurrentProjectInfo(String projectId) {
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        if (!Objects.isNull(projectId)) {
            state.addBatch(StringUtil.format("delete from bamboo_class WHERE project_id = '{}';", projectId));
            state.addBatch(StringUtil.format("delete from bamboo_method WHERE project_id = '{}';", projectId));
            state.executeBatch();
        }
        state.close();
        conn.close();
    }

    private static Set<String> queryTables() throws SQLException {
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        ResultSet resultSet = state.executeQuery("select * from sqlite_master  where  type='table';");
        Set<String> tableInfos = new HashSet<>();
        while (resultSet.next()) {
            tableInfos.add(resultSet.getString("tbl_name"));
        }
        resultSet.close();
        state.close();
        conn.close();
        return tableInfos;
    }

    private static Set<String> queryDictAll() throws SQLException {
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        ResultSet resultSet = state.executeQuery("select key||value  onlyOne from bamboo_dict ;");
        Set<String> dicts = new HashSet<>();
        while (resultSet.next()) {
            dicts.add(resultSet.getString("onlyOne"));
        }
        resultSet.close();
        state.close();
        conn.close();
        return dicts;
    }

    private static Map<String, Map<String, Map<String, Map<String, String>>>> querySetting() throws SQLException {
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        ResultSet resultSet = state.executeQuery("select f.name,ais.class_path,ais.scope,aps.name as property from framework f left join annotation_info_setting ais on f.id = ais.framework_id left join annotation_param_setting aps on ais.id = aps.annotation_info_setting_id");
        Map<String, Map<String, Map<String, Map<String, String>>>> map = new HashMap<>();

        while (resultSet.next()) {
            String frameworkName = resultSet.getString("name");
            String classPath = resultSet.getString("class_path");
            String scope = resultSet.getString("scope");
            String property = resultSet.getString("property");
            Map<String, Map<String, Map<String, String>>> frameworkMap = map.get(frameworkName);
            if (CollUtil.isEmpty(frameworkMap)) {
                map.put(frameworkName, new HashMap<>());
                frameworkMap = map.get(frameworkName);
            }
            if (StrUtil.isEmpty(scope)) {
                continue;
            }
            Map<String, Map<String, String>> scopeMap = frameworkMap.get(scope);
            if (CollUtil.isEmpty(scopeMap)) {
                frameworkMap.put(scope, new HashMap<>());
                scopeMap = frameworkMap.get(scope);
            }
            if (StrUtil.isEmpty(classPath)) {
                continue;
            }
            Map<String, String> classPathMap = scopeMap.get(classPath);
            if (CollUtil.isEmpty(classPathMap)) {
                scopeMap.put(classPath, new HashMap<>());
                classPathMap = scopeMap.get(classPath);
            }
            if (StrUtil.isEmpty(property)) {
                continue;
            }
            classPathMap.put(property, property);

        }
        resultSet.close();
        state.close();
        conn.close();
        return map;
    }

    @SneakyThrows
    public List<BambooDict> getAllDictByCode(List<String> codes) {
        if (codes.size() < 1) {
            return new ArrayList<>();
        }
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        StringBuilder sql = new StringBuilder();
        sql.append("select *  from bamboo_dict where key ");
        if (codes.size() > 0) {
            sql.append("in( ");
            for (int i = 0; i < codes.size(); i++) {
                if (i != 0) {
                    sql.append(",");
                }
                sql.append("'");
                sql.append(codes.get(i));
                sql.append("'");
            }
            sql.append(")");
        }
        sql.append(";");
        ResultSet resultSet = state.executeQuery(sql.toString());
        List<BambooDict> dicts = new ArrayList<>();
        while (resultSet.next()) {

            String id = resultSet.getString("id");
            String key = resultSet.getString("key");
            String value = resultSet.getString("value");
            String description = resultSet.getString("description");
            BambooDict bambooDict = new BambooDict();
            bambooDict.setId(id);
            bambooDict.setKey(key);
            bambooDict.setDescription(description);
            bambooDict.setValue(value);
            dicts.add(bambooDict);
        }
        resultSet.close();
        state.close();
        conn.close();
        return dicts;
    }

    @SneakyThrows
    @Override
    public void updateIsShowDesc(Boolean isShowDesc) {
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        state.executeUpdate("UPDATE bamboo_dict SET  value = '" + isShowDesc + "' WHERE key = 'isShowDesc';");
        state.close();
        conn.close();
    }

    @Override
    public void updateDescFrameworkSequence(List<BambooDict> dicts) throws SQLException {
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        for (int i = 0; i < dicts.size(); i++) {
            state.addBatch(" UPDATE bamboo_dict SET description = " + i + " WHERE id = '" + dicts.get(i).getId() + "';");
        }
        state.executeBatch();
        state.close();
        conn.close();
    }

    @SneakyThrows
    @Override
    public boolean selectIsShowDesc() {
        List<BambooDict> allDictByCode = getAllDictByCode(Arrays.asList(StringConstant.IS_SHOW_DESC));
        if (CollUtil.isEmpty(allDictByCode)) {
            Connection conn = SqliteConfig.getConnection();
            Statement state = conn.createStatement();
            state.execute("INSERT INTO bamboo_dict ( key, value, description, other_info) VALUES ( 'isShowDesc', 'true', '是否开启描述', null);");
            state.close();
            conn.close();
            return true;
        } else {
            return allDictByCode.get(0).getValue().equals("true");
        }
    }

    @SneakyThrows
    @Override
    public void renameProject(Integer projectId, String rename) {
        StringBuffer str = new StringBuffer();
        str.append(" UPDATE bamboo_project SET ");
        ProjectOtherInfo projectOtherInfo = new ProjectOtherInfo();
        projectOtherInfo.getDesc().put(StringConstant.RENAME, rename);
        str.append(StringUtil.format(" other_info = '{}'", JSONObject.toJSON(projectOtherInfo)));
        str.append(StringUtil.format(" WHERE id = {};", projectId));
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        state.executeUpdate(str.toString());
        conn.close();
    }

    @SneakyThrows
    @Override
    public void renameClass(String classId, String rename) {
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        ResultSet resultSet = state.executeQuery("select persistence from bamboo_class where  id='" + classId + "';");
        ClassPersistenceInfo persistenceInfo = null;
        while (resultSet.next()) {
            String persistence = resultSet.getString("persistence");
            if (StrUtil.isNotBlank(persistence)) {
                persistenceInfo = JSONObject.parseObject(persistence, ClassPersistenceInfo.class);
            }
        }
        if (persistenceInfo != null) {
            persistenceInfo.setRename(rename);
            state.execute("UPDATE bamboo_class SET persistence = '" + JSONObject.toJSON(persistenceInfo) + "' WHERE id = '" + classId + "';");

        }
        state.close();
        conn.close();
    }

    @SneakyThrows
    @Override
    public void renameMethod(String methodId, String rename) {
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        ResultSet resultSet = state.executeQuery("select persistence from bamboo_method where  id='" + methodId + "';");
        MethodPersistenceInfo persistenceInfo = null;
        while (resultSet.next()) {
            String persistence = resultSet.getString("persistence");
            if (StrUtil.isNotBlank(persistence)) {
                persistenceInfo = JSONObject.parseObject(persistence, MethodPersistenceInfo.class);
            }
        }
        if (persistenceInfo != null) {
            persistenceInfo.setRename(rename);
            state.execute("UPDATE bamboo_method SET persistence = '" + JSONObject.toJSON(persistenceInfo) + "' WHERE id = '" + methodId + "';");

        }
        state.close();
        conn.close();
    }

    @SneakyThrows
    public ProjectInfo saveProject(ProjectInfo projectInfo) {
        StringBuffer str = new StringBuffer();
        str.append("INSERT INTO bamboo_project (project_name, project_path,other_info) VALUES (");
        str.append(StringUtil.format("'{}',", projectInfo.getProjectName()));
        str.append(StringUtil.format("'{}',", projectInfo.getProjectPath()));
        str.append(StringUtil.format("'{}')", JSONObject.toJSON(new ProjectOtherInfo())));
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        state.executeUpdate(str.toString());
        conn.close();
        return queryProject(projectInfo.getProjectPath(), projectInfo.getProjectName());
    }

    @SneakyThrows
    public Map<String, PersistenceInfo> getCurrentProjectAllPersistenceInfo(String projectId) {
        Connection conn = SqliteConfig.getConnection();
        Statement state = conn.createStatement();
        ResultSet resultSet = state.executeQuery("SELECT bc.persistence AS classPersistence, bc.class_path, bm.persistence AS methodPersistence, bm.method_name AS methodName, bm.method_params AS methodParams FROM bamboo_class bc LEFT JOIN bamboo_method bm ON bc.id = bm.class_id WHERE bc.project_id = '" + projectId + "'");

        Map<String, PersistenceInfo> persistenceInfoMap = new HashMap<>();
        while (resultSet.next()) {
            String classPersistence = resultSet.getString("classPersistence");
            String classPath = resultSet.getString("class_path");
            String methodPersistence = resultSet.getString("methodPersistence");
            String methodName = resultSet.getString("methodName");
            String methodParams = resultSet.getString("methodParams");
            PersistenceInfo persistenceInfo = persistenceInfoMap.getOrDefault(classPath, new PersistenceInfo());
            persistenceInfo.setClassPersistenceInfo(JSONObject.parseObject(classPersistence, ClassPersistenceInfo.class));
            Map<String, Map<String, MethodPersistenceInfo>> methodPersistenceInfoMap = persistenceInfo.getMethodPersistenceInfoMap();
            Map<String, MethodPersistenceInfo> stringMethodPersistenceInfoMap = methodPersistenceInfoMap.getOrDefault(methodName, new HashMap<>());
            stringMethodPersistenceInfoMap.put(methodParams, JSONObject.parseObject(methodPersistence, MethodPersistenceInfo.class));
            methodPersistenceInfoMap.put(methodName, stringMethodPersistenceInfoMap);
            persistenceInfoMap.put(classPath, persistenceInfo);

        }
        state.close();
        conn.close();
        return persistenceInfoMap;
    }
}
